﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using DbConnect.Sql;


namespace DbConnect
{
    public class DataAccess
    {

        #region "Private attributes"

        private const string _sConnect = "Data Source=DnD.s3db";

        private SQLiteConnection _DataConnection;

        private SQLiteCommand _DataCommand;

        private List<SQLiteParameter> _param;


        #endregion

        #region "Properties"

        private SQLiteConnection Connection
        {
            get
            {
                if (_DataConnection == null)
                {
                    _DataConnection = new SQLiteConnection(_sConnect);
                }
                if (_DataConnection.State == System.Data.ConnectionState.Closed)
                {
                    _DataConnection.Open();
                }
                return _DataConnection;
            }
        }

        private SQLiteCommand Command
        {
            get
            {
                if (_DataCommand == null || _DataCommand.Connection == null)
                {
                    _DataCommand = new SQLiteCommand(Connection);
                }
                
                return _DataCommand;
            }
        }


        #endregion

        #region "Events"
        #endregion

        #region "Public functions"

        public DataAccess()
        {
            _param = new List<SQLiteParameter>();
        }

        public int CreateDataBase()
        {
            Logger.Log.TraceAction("DataAccess.CreateDataBase", "");
            int i = 0;
            try
            {
                if (CheckDataBaseExists())
                {
                    return 2;
                }

                Command.CommandText = SqlReader("Sql/Create.sql");

                
                 Command.ExecuteNonQuery();
                 i = 1;
            }
            catch (Exception ex)
            {
                Logger.Log.TraceError("DataAccess.CreateDatase", ex);
                i = 0;

            }
            finally
            {
                Connection.Clone();
                Command.Dispose();
            }
            return i;
        }

        public int CleanDataBase()
        {
            Logger.Log.TraceAction("DataAccess.CleanDataBase", "");
            int i = 0;
            try
            {
                if  (!CheckDataBaseExists())
                {
                    return 0;
                }

                Command.CommandText = SqlReader("Sql/Delete.sql");


                Command.ExecuteNonQuery();
                i = 1;
            }
            catch (Exception ex)
            {
                Logger.Log.TraceError("DataAccess.CleanDataBase", ex);
                i = 0;

            }
            finally
            {
                Connection.Clone();
                Command.Dispose();
            }
            return i;
        }

        public bool CheckDataBaseExists()
        {
            if (System.IO.File.Exists("DnD.s3db"))
                return true;
            else
                return false;
        }

        private String SqlReader(String fileName)
        {
            var sql = "";
            System.IO.StreamReader reader = null;
            try
            {
                Logger.Log.TraceAction("DataAccess.SqlReader", fileName);
                reader = new System.IO.StreamReader(fileName);
                sql = reader.ReadToEnd();
            }
            catch (Exception ex)
            {
                Logger.Log.TraceError("DataAccess.SqlReader", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
            }
            return sql;
        }

        public int Insert(String Request)
        {
            Logger.Log.TraceAction("DataAccess.Insert", "");
            var Ok = 0;
            try
            {
                if (_param.Count == 0)
                    return Ok;

                Command.CommandText = Request;
                Command.Parameters.AddRange(_param.ToArray());
                Ok = Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.Log.TraceError("DataAccess.Insert", ex);
            }
            finally
            {
                Connection.Close();
                Command.Dispose();
            }

            return Ok;
        }

        public int Update(String Request)
        {
            Logger.Log.TraceAction("DataAccess.Update", "");
            var Ok = 0;
            try
            {
                if (_param.Count == 0)
                    return Ok;

                Command.CommandText = Request;
                Command.Parameters.AddRange(_param.ToArray());

                Ok = Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.Log.TraceError("DataAccess.Update", ex);
            }
            finally
            {
                Connection.Close();
                Command.Dispose();
            }

            return Ok;
        }

        public int Delete(String Request)
        {
            Logger.Log.TraceAction("DataAccess.Delete", "");
            var Ok = 0;
            try
            {
                if (_param.Count == 0)
                    return Ok;
                Command.CommandText = Request;
                Command.Parameters.AddRange(_param.ToArray());

                Ok = Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.Log.TraceError("DataAccess.Delete", ex);
            }
            finally
            {
                Connection.Close();
                Command.Dispose();
            }

            return Ok;
        }

        public System.Data.DataTable SelectWithParam(String Request)
        {
            var dt = new System.Data.DataTable();
            try
            {
                if (_param.Count == 0)
                    return null;
                Logger.Log.TraceAction("DataAcess.Select", "");
                Command.CommandText = Request;
                Command.Parameters.AddRange(_param.ToArray());

                var reader = Command.ExecuteReader();
                dt.Load(reader);
                reader.Close();
            }
            catch (Exception ex)
            {
                Logger.Log.TraceError("DataAccess.Select", ex);
            }
            finally
            {
                Connection.Close();
                Command.Clone();
            }


            return dt;
        }

        public System.Data.DataTable Select(String Request)
        {
            var dt = new System.Data.DataTable();
            try
            {
                Logger.Log.TraceAction("DataAcess.Select", Request);
                Command.CommandText = Request;

                var reader = Command.ExecuteReader();
                dt.Load(reader);
                reader.Close();
            }
            catch (Exception ex)
            {
                Logger.Log.TraceError("DataAccess.Select", ex);
            }
            finally
            {
                Connection.Close();
                Command.Clone();
            }


            return dt;
        }

        public void AddParam(String Name, Object value)
        {

            try
            {
                Logger.Log.TraceAction("DataAccess.GetParam", "");
                this._param.Add(new System.Data.SQLite.SQLiteParameter(Name, value));
            }
            catch (Exception ex)
            {
                Logger.Log.TraceError("DataAccess.GetParam", ex);

            }
            finally
            {
            }
            

        }

        public int GetLastIDInserted(String TableName)
        {


            try
            {
                Logger.Log.TraceAction("DataAccess.GetLastIDInserted", "");
                var req = SqlRequest.Select + SqlRequest.Distinct + SqlRequest.LastIDInserted + SqlRequest.From
                    + TableName;
                var table = Select(req);

                return table.Rows[0].GetIntValue("ID");
            }
            catch (Exception ex)
            {
                Logger.Log.TraceError("DataAccess.GetLastIDInserted", ex);
                return -1;

            }
            finally
            {
            }
           
        }




        #endregion

        #region "Private functions"
        #endregion

    }
}
